﻿using System;
using System.Data.OleDb;
using System.Collections.Generic;
using System.IO;
using System.Data;
using Excel2003 = Net.SourceForge.Koogra.Excel;
using Excel2007 = Net.SourceForge.Koogra.Excel2007;
using org.in2bits.MyXls;
using Eagle.Common;

namespace Eagle.Module.Office
{
    /// <summary>
    /// Excel操作类，包含对Excel2007的处理
    /// </summary>
    public class Excel
    {
        #region 导入Excel

        #region 获取Excel的所有Sheet

        /// <summary>
        /// 获取Excel的所有Sheet
        /// </summary>
        /// <param name="fileName">excel的物理路径</param>
        /// <returns></returns>
        public static List<Sheet> GetSheetName(string fileName)
        {
            List<Sheet> sheets = new List<Sheet>(5);
            string extensionName = Path.GetExtension(fileName);
            //如果是Excel2007
            if (string.Compare(extensionName, ".xlsx", true) == 0)
            {
                Excel2007.Workbook workbook = new Net.SourceForge.Koogra.Excel2007.Workbook(fileName);
                int sheetIndex = 0;
                foreach (Excel2007.Worksheet worksheet in workbook.GetWorksheets())
                {
                    Sheet sheet = new Sheet();
                    sheet.Index = sheetIndex;
                    sheet.Name = worksheet.Name;
                    sheets.Add(sheet);
                    sheetIndex++;
                }
            }
            else//默认处理excel2003
            {
                Excel2003.Workbook workbook = new Net.SourceForge.Koogra.Excel.Workbook(fileName);
                int sheetIndex = 0;
                foreach (Excel2003.Worksheet worksheet in workbook.Sheets)
                {
                    Sheet sheet = new Sheet();
                    sheet.Index = sheetIndex;
                    sheet.Name = worksheet.Name;
                    sheets.Add(sheet);
                    sheetIndex++;
                }
            }
            return sheets;
        }

        #endregion

        #region 获取单个Sheet的所有列名
        /// <summary>
        /// 获取单个Sheet的所有列名
        /// </summary>
        /// <param name="fileName">文件物理路径</param>
        /// <param name="sheetIndex">Sheet页的索引</param>
        /// <returns></returns>
        public static List<Column> GetSheetColumnName(string fileName, int sheetIndex)
        {
            List<Column> columns = new List<Column>(10);
            string extensionName = Path.GetExtension(fileName);
            //如果是Excel2007
            if (string.Compare(extensionName, ".xlsx", true) == 0)
            {
                Excel2007.Workbook workbook = new Net.SourceForge.Koogra.Excel2007.Workbook(fileName);
                Excel2007.Worksheet worksheet = workbook.GetWorksheet(sheetIndex);
                Excel2007.CellMap cellmap = worksheet.CellMap;
                uint firstRow = cellmap.FirstRow;
                uint firstColumn = cellmap.FirstCol;
                uint lastColumn = cellmap.LastCol;
                for (uint i = firstColumn; i <= lastColumn; i++)
                {
                    Column column = new Column();
                    column.Index = (int)i;
                    column.Name = worksheet.GetFormattedValue(firstRow, i);
                    columns.Add(column);
                }
            }
            else//默认处理excel2003
            {
                Excel2003.Workbook workbook = new Net.SourceForge.Koogra.Excel.Workbook(fileName);
                Excel2003.Worksheet worksheet = workbook.Sheets[sheetIndex];
                Excel2003.RowCollection rowcellection = worksheet.Rows;
                uint firstRow = rowcellection.MinRow;
                Excel2003.CellCollection cellcollection = worksheet.Rows[firstRow].Cells;
                uint firstColumn = cellcollection.MinCol;
                uint lastColumn = cellcollection.MaxCol;
                for (uint i = firstColumn; i <= lastColumn; i++)
                {
                    Column column = new Column();
                    column.Index = (int)i;
                    column.Name = cellcollection[i].Value == null ? string.Empty : cellcollection[i].Value.ToString();
                    columns.Add(column);
                }
            }
            return columns;
        }
        #endregion

        #region 获取单个Excel中的数据
        /// <summary>
        /// 获取单个Excel中的数据
        /// </summary>
        /// <param name="fileName">物理文件路径</param>
        /// <param name="sheetIndex">sheet页的索引</param>
        /// <param name="columnNameList">列名组合，如 [学生信息] as studentinfo,[年龄] as age</param>
        /// <returns></returns>
        public static DataTable GetSheetData(string fileName, int sheetIndex, string columnNameList)
        {
            string extensionName = Path.GetExtension(fileName);
            DataTable dt = new DataTable();
            //如果是Excel2007
            if (string.Compare(extensionName, ".xlsx", true) == 0)
            {
                Excel2007.Workbook workbook = new Net.SourceForge.Koogra.Excel2007.Workbook(fileName);
                Excel2007.Worksheet worksheet = workbook.GetWorksheet(sheetIndex);
                string sheetName = worksheet.Name;
                string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + fileName + ";Extended Properties = &apos;Excel 12.0;HDR=yes;IMEX=1 &apos;";
                using (OleDbConnection oledbconnection = new OleDbConnection(conn))
                {
                    OleDbDataAdapter oledbdataadapter = null;
                    try
                    {
                        oledbdataadapter = new OleDbDataAdapter("select " + columnNameList + " from [" + sheetName + "$]", oledbconnection);
                        oledbconnection.Open();
                        oledbdataadapter.Fill(dt);
                    }
                    catch (Exception e)
                    {
                        EagleUtils.WriteErrorToTxt(e.ToString());
                    }
                    finally
                    {
                        if (oledbdataadapter != null)
                            oledbdataadapter.Dispose();
                    }
                }
            }
            else//默认处理excel2003
            {
                Excel2003.Workbook workbook = new Net.SourceForge.Koogra.Excel.Workbook(fileName);
                string sheetName = workbook.Sheets[sheetIndex].Name;
                string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties = &apos;Excel 8.0;HDR=yes;IMEX=1 &apos;";
                using (OleDbConnection oledbconnection = new OleDbConnection(conn))
                {
                    OleDbDataAdapter oledbdataadapter = null;
                    try
                    {
                        oledbdataadapter = new OleDbDataAdapter("select " + columnNameList + " from [" + sheetName + "$]", oledbconnection);
                        oledbconnection.Open();

                        oledbdataadapter.Fill(dt);
                    }
                    catch (Exception e)
                    {
                        EagleUtils.WriteErrorToTxt(e.ToString());
                    }
                    finally
                    {
                        if (oledbdataadapter != null)
                            oledbdataadapter.Dispose();
                    }
                }
            }
            return dt;
        }
        #endregion

        #endregion

        #region 导出excel文件，采用开源组件org.in2bits.MyXls
        /// <summary>
        /// 导出excel文件，列名采用DataTable中的列名
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="fileName">文件物理路径</param>
        /// <param name="sheetName">sheet页的名称，如：学生信息</param>
        /// <param name="webDownload">是否启动Web下载</param>
        public static void ExportExcelByMyXls(DataTable dt, string fileName, string sheetName, bool webDownload)
        {
            List<string> columnNameList = new List<string>(dt.Columns.Count);
            foreach (DataColumn column in dt.Columns)
            {
                columnNameList.Add(column.ColumnName);
            }
            ExportExcelByMyXls(dt, string.Join(",", columnNameList.ToArray()), fileName,sheetName, webDownload);
        }


        /// <summary>
        /// 导出excel文件，采用开源组件org.in2bits.MyXls
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="columnName">标题，多标题用半角“,”隔开，如 学号,班级,姓名，与数据表中的列要一一对应</param>
        /// <param name="fileName">文件物理路径</param>
        /// <param name="sheetName">sheet页的名称，如：学生信息</param>
        /// <param name="webDownload">是否启动Web下载</param>
        public static void ExportExcelByMyXls(DataTable dt, string columnName, string fileName, string sheetName, bool webDownload)
        {
            XlsDocument xlsdocument = new org.in2bits.MyXls.XlsDocument();
            xlsdocument.SummaryInformation.Author = "Eagle Architecture";
            xlsdocument.SummaryInformation.CreateTimeDate = DateTime.Now;
            xlsdocument.SummaryInformation.Comments = "Use MyXls,You can create a xls document very easy";
            xlsdocument.SummaryInformation.LastSavedBy = "Elevenbus";
            xlsdocument.SummaryInformation.NameOfCreatingApplication = "Eagle";
            xlsdocument.DocumentSummaryInformation.Company = "FeiYing co.,Ltd.";
            Workbook workbook = xlsdocument.Workbook; ;
            Worksheets worksheets = workbook.Worksheets;
            string[] columnNameList = columnName.Split(',');
            int rowsCount = dt.Rows.Count;
            int columnCount = dt.Columns.Count;
            int sheetSize = 50000;//定义每个sheet页容量大小
            if (rowsCount > sheetSize)
            {
                int x = 1, y = 1, z = 0;
                for (int k = sheetSize; k < rowsCount; k = k + sheetSize)
                {
                    Worksheet worksheet = worksheets.Add(sheetName + x.ToString());
                    Cells cells = worksheet.Cells;
                    for (int i = 1; i <= columnNameList.Length; i++)
                    {
                        cells.Add(1, i, columnNameList[i - 1].ToString());
                    }

                    for (int i = k - sheetSize + 2; i <= k + 1; i++)
                    {
                        y++;
                        for (int j = 1; j <= columnCount; j++)
                        {
                            if (string.Compare(dt.Columns[j - 1].DataType.ToString(), "System.String", true).Equals(0))
                            {
                                cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                            }
                            else
                            {
                                cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                            }
                        }
                    }
                    x++;
                    y = 1;
                    z = k;//当k不再循环时的k值
                }
                if (rowsCount - z > 0)//如果还有剩余，则再添加一个sheet
                {
                    int w = rowsCount - z;
                    Worksheet worksheet = worksheets.Add(sheetName + x.ToString());
                    Cells cells = worksheet.Cells;
                    for (int i = 1; i <= columnNameList.Length; i++)
                    {
                        cells.Add(1, i, columnNameList[i - 1].ToString());
                    }
                    for (int i = z + 2; i <= rowsCount + 1; i++)
                    {
                        y++;
                        for (int j = 1; j <= columnCount; j++)
                        {
                            if (string.Compare(dt.Columns[j - 1].DataType.ToString(), "System.String", true).Equals(0))
                            {
                                cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                            }
                            else
                            {
                                cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                            }
                        }
                    }
                }
            }
            else
            {
                Worksheet worksheet = worksheets.Add(sheetName);
                Cells cells = worksheet.Cells;
                for (int i = 1; i <= columnNameList.Length; i++)
                {
                    cells.Add(1, i, columnNameList[i - 1].ToString());
                }
                for (int i = 2; i <= rowsCount + 1; i++)
                {
                    for (int j = 1; j <= columnCount; j++)
                    {
                        if (string.Compare(dt.Columns[j - 1].DataType.ToString(), "System.String", true).Equals(0))
                        {
                            cells.Add(i, j, dt.Rows[i - 2][j - 1].ToString());
                        }
                        else
                        {
                            cells.Add(i, j, dt.Rows[i - 2][j - 1].ToString());
                        }
                    }
                }
            }
            dt.Dispose();
            EagleUtils.CreateDirectory(fileName);
            if (File.Exists(fileName))
                File.SetAttributes(fileName, FileAttributes.Archive);
            xlsdocument.FileName = fileName;
            xlsdocument.Save(true);
            if (webDownload)
                EagleUtils.DownloadFile(fileName, "application/vnd.ms-excel");
        }
        #endregion

        #region EXCEL用到的实体类
        /// <summary>
        /// Sheet实体类
        /// </summary>
        public class Sheet
        {
            /// <summary>
            /// Sheet页的索引
            /// </summary>
            public int Index { get; set; }
            /// <summary>
            /// Sheet页的名称
            /// </summary>
            public string Name { get; set; }
        }
        /// <summary>
        /// Sheet页中列的实体类
        /// </summary>
        public class Column
        {
            /// <summary>
            /// Sheet页中列的索引
            /// </summary>
            public int Index { get; set; }
            /// <summary>
            ///Sheet页中列的名称
            /// </summary>
            public string Name { get; set; }
        }
        #endregion

    }
}
